package com.dp.service.impl3;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.dp.pojo.GroupCatalogVo;
import com.dp.util.DataMap;
import com.dp.vo.NewsCatlog;
import com.dp.vo.NewsGroup;
import com.dp.vo.TAreainfo;

@Service
@Transactional
public class CataloggroupServiceImpl extends BaseImpl {

	/**
	 * 目录关联用户组 记录数
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return int
	 */
	/*public int findAllGroupCatalogCount(DataMap hashMap){
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		GroupCatalogVo groupCatalog=(GroupCatalogVo) hashMap.get("groupCatalog");
		int iCount = 0;
		StringBuffer sqlS = new StringBuffer("select ng.groupruleid,ngc.catlogid,ng.grouprulename,nc.catlogname,nc.catlogversion from news_group_catlog ngc join news_group ng on ngc.groupruleid=ng.groupruleid join news_catlog nc on ngc.catlogid=nc.catlogid ");
		if(groupCatalog!=null){
			if(groupCatalog.getCatlogname()!=null&& groupCatalog.getCatlogname().trim().length() > 0){
				sqlS.append(" and nc.catlogname like '%"
						+ groupCatalog.getCatlogname().toString().trim() + "%'");
			}
			if(groupCatalog.getGroupname()!=null&& groupCatalog.getGroupname().trim().length() > 0){
				sqlS.append(" and ng.grouprulename like '%"
						+ groupCatalog.getGroupname().toString().trim() + "%'");
			}
		}
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				sqlS.append(" order by "+jpage.getSidx()+" "+jpage.getSord());
			}
		}
		
		try {
			this.init();
			rs1=stmt.executeQuery(sqlS.toString());
			while(rs1.next()){
				iCount++;
			}
		
		conn.commit(); 
		} catch (Exception e) {
			logger.error("findAllGroupCatalogCount fail !!!" + e.getMessage());
			e.printStackTrace();
			if(conn!=null){
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				} 
			}
		} finally {
			this.closeAll();
		}
		return iCount;
	}*/
	
	/**
	 * Find all the GroupCatalogVo
	 * @param hashMap
	 * @param firstrow
	 * @param maxrow
	 * @return List<GroupCatalogVo>
	 */
	/*public List<GroupCatalogVo> findAllGroupCatalog(DataMap hashMap,Integer firstrow,
			Integer maxrow){
		JsonPage jpage=(JsonPage) hashMap.get("jpage");
		GroupCatalogVo groupCatalog=(GroupCatalogVo) hashMap.get("groupCatalog");
		StringBuffer sqlS = new StringBuffer("select ng.groupruleid,ngc.catlogid,ng.grouprulename,nc.catlogname,nc.catlogversion from news_group_catlog ngc join news_group ng on ngc.groupruleid=ng.groupruleid join news_catlog nc on ngc.catlogid=nc.catlogid ");
		if(groupCatalog!=null){
			if(groupCatalog.getCatlogname()!=null&& groupCatalog.getCatlogname().trim().length() > 0){
				sqlS.append(" and nc.catlogname like '%"
						+ groupCatalog.getCatlogname().toString().trim() + "%'");
			}
			if(groupCatalog.getGroupname()!=null&& groupCatalog.getGroupname().trim().length() > 0){
				sqlS.append(" and ng.grouprulename like '%"
						+ groupCatalog.getGroupname().toString().trim() + "%'");
			}
		}
		if(jpage!=null){
			if(jpage.getSidx()!=null&&!"".equals(jpage.getSidx())){
				sqlS.append(" order by "+jpage.getSidx()+" "+jpage.getSord());
			}
		}
		if(firstrow!=null&&maxrow!=null){
			sqlS.append(" limit "+firstrow+","+maxrow+"");
		}
		List<GroupCatalogVo> list=new ArrayList<GroupCatalogVo>();
		try {
			this.init();
			rs1=stmt.executeQuery(sqlS.toString());
			GroupCatalogVo groupCatalog_temp = null;
			while(rs1.next()){
				groupCatalog_temp=new GroupCatalogVo();
				groupCatalog_temp.setGroupid(rs1.getInt(1));
				groupCatalog_temp.setCatlogid(rs1.getInt(2));
				groupCatalog_temp.setGroupname(rs1.getString(3));
				groupCatalog_temp.setCatlogname(rs1.getString(4));
				groupCatalog_temp.setCatlogversion(rs1.getInt(5));
				list.add(groupCatalog_temp);
			}
		
		conn.commit(); 
		} catch (Exception e) {
			logger.error("findAllGroupCatalog fail !!!" + e.getMessage());
			e.printStackTrace();
			if(conn!=null){
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				} 
			}
		} finally {
			this.closeAll();
		}
		return list;
	}*/
	
	
	/**
	 * 增加Catalog和用户组的关系
	 * @param dataMap
	 * @return
	 */
	/*public Boolean addCataloggroup(DataMap dataMap) {
		GroupCatalogVo groupCatalog=(GroupCatalogVo) dataMap.get("groupCatalog");
		String groupids= (String)dataMap.get("groupids");
		Boolean resultb=true;
		try {
			this.init();
			this.sql="SELECT catlogid FROM news_group_catlog WHERE catlogid=?";
			this.prep=this.conn.prepareStatement(sql);	
			this.prep.setInt(1, groupCatalog.getCatlogid());
			this.rs1=prep.executeQuery();
			if(this.rs1.next()){
				return null;
			}
			//增加Catalog和用户组的关系
			String[] selected_platform=null;
			if(!"".equals(groupids)){
				selected_platform=groupids.split(",");
			}
			if (selected_platform!= null) {
				this.sql="insert into news_group_catlog (groupruleid,catlogid) values (?,?)";
				this.prep=this.conn.prepareStatement(sql);
				for (int i = 0; i < selected_platform.length; i++) {
					int groupid = Integer.parseInt(selected_platform [i]) ;
					prep.setInt(1, groupid);
					prep.setInt(2, groupCatalog.getCatlogid());
					prep.addBatch();
					}
				prep.executeBatch();
			}
			
			conn.commit(); 
		} catch (Exception e) {
			logger.info("addCataloggroup fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
			if(conn!=null){
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				} 
			}
		} finally {
			this.closeAll();
		}
		return resultb;
	}*/
	
	/**
	 * 修改目录与用户组的关系
	 * @param dataMap
	 * @return
	 */
	public boolean updateCataloggroup(DataMap dataMap) {
		/*GroupCatalogVo groupCatalog=(GroupCatalogVo) dataMap.get("groupCatalog");
		String groupids= (String)dataMap.get("groupids");
		boolean resultb=true;
		try {
			
			//增加新的Catalog和用户组的关系
			String[] selected_platform=null;
			if(!"".equals(groupids)){
				selected_platform=groupids.split(",");
			}
			String delidstr = "";
			if(selected_platform.length <=0){
				return resultb;
			}
			for (int i = 0; i < selected_platform.length; i++) {
				delidstr+="'"+selected_platform [i]+"',";
			}
			delidstr = delidstr.substring(0, delidstr.length()-1);
			this.init();
			String deletesql="DELETE FROM news_group_catlog WHERE groupruleid in ("+delidstr+")";
			logger.info("deletesql: " + deletesql);
			this.prep=this.conn.prepareStatement(deletesql);
			prep.executeUpdate();
			
			
			//删除老的Catalog和用户组的关系
			this.sql="DELETE FROM news_group_catlog WHERE catlogid=?";
			this.prep=this.conn.prepareStatement(sql);	
			this.prep.setInt(1, groupCatalog.getCatlogid());
			prep.executeUpdate();
			
			
			
			
			if (selected_platform!= null) {
				this.sql="insert into news_group_catlog (groupruleid,catlogid) values (?,?)";
				this.prep=this.conn.prepareStatement(sql);
				for (int i = 0; i < selected_platform.length; i++) {
					int groupid = Integer.parseInt(selected_platform [i]) ;
					prep.setInt(1, groupid);
					prep.setInt(2, groupCatalog.getCatlogid());
					prep.addBatch();
					}
				prep.executeBatch();
			}
			
			conn.commit(); 
		} catch (Exception e) {
			logger.info("updateCataloggroup fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
			if(conn!=null){
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				} 
			}
		} finally {
			this.closeAll();
		}
		return resultb;*/
		
		
		GroupCatalogVo groupCatalog=(GroupCatalogVo) dataMap.get("groupCatalog");
		String groupids= (String)dataMap.get("groupids");
		boolean resultb=true;
		try {
			
			//增加新的Catalog和用户组的关系
			String[] selected_platform=null;
			if(!"".equals(groupids)){
				selected_platform=groupids.split(",");
			}
			String delidstr = "";
			if(selected_platform.length <=0){
				return resultb;
			}
			for (int i = 0; i < selected_platform.length; i++) {
				delidstr+="'"+selected_platform [i]+"',";
			}
			delidstr = delidstr.substring(0, delidstr.length()-1);
			String deletesql="DELETE FROM news_group_catlog WHERE groupruleid in ("+delidstr+")";
			logger.info("deletesql: " + deletesql);
			this.em.createNativeQuery(deletesql).executeUpdate();
			
			//删除老的Catalog和用户组的关系
			this.sql="DELETE FROM news_group_catlog WHERE catlogid=?";
			Query query=em.createNativeQuery(sql);
			query.setParameter(1, groupCatalog.getCatlogid());
			query.executeUpdate();
			
			if (selected_platform!= null) {
				this.sql="insert into news_group_catlog (groupruleid,catlogid) values (?,?)";
				query=em.createNativeQuery(sql);
				for (int i = 0; i < selected_platform.length; i++) {
					int groupid = Integer.parseInt(selected_platform [i]) ;
					query.setParameter(1, groupid);
					query.setParameter(2, groupCatalog.getCatlogid());
					query.executeUpdate();
				}
			}
			
		} catch (Exception e) {
			logger.info("updateCataloggroup fail !!!" + e.getMessage());
			e.printStackTrace();
			resultb=false;
		} 
		return resultb;
	}
	
	/**
	 * 目录已选关联组
	 * @param catlogid
	 * @return
	 */
	public List<NewsGroup> getSelectedgroupListbyid(Integer catlogid) {
		/*StringBuffer sqlS = new StringBuffer("select ng.groupruleid,ng.grouprulename,ng.mlocale,ng.marea from news_group_catlog ngc join news_group ng on ngc.groupruleid=ng.groupruleid join news_catlog nc on ngc.catlogid=nc.catlogid ");
		if(catlogid!=null){
			sqlS.append(" and nc.catlogid="
					+ catlogid + "");
		}
		List<NewsGroup> list=new ArrayList<NewsGroup>();
		try {
			this.init();
			rs1=stmt.executeQuery(sqlS.toString());
			NewsGroup group_temp = null;
			sqlS=new StringBuffer("select areaid,areaname from t_areainfo where areaid=?");
			prep=conn.prepareStatement(sqlS.toString());
			while(rs1.next()){
				group_temp=new NewsGroup();
				group_temp.setGroupruleid(rs1.getInt(1));
				group_temp.setGrouprulename(rs1.getString(2));
				group_temp.setMlocale(rs1.getString(3));
				if(rs1.getString(4)!=null&&!"".equals(rs1.getString(4))){
					prep.setString(1, rs1.getString(4));
					rs2=prep.executeQuery(); 
					TAreainfo areainfo = null;
					if(rs2.next()){
						areainfo=new TAreainfo();
						areainfo.setAreaid(rs2.getInt(1));
						areainfo.setAreaname(rs2.getString(2));
					}
					if(areainfo!=null){
						group_temp.setMarea(areainfo);
					}
				}
				list.add(group_temp);
			}
		
		conn.commit(); 
		} catch (Exception e) {
			logger.error("getSelectedgroupListbyid fail !!!" + e.getMessage());
			e.printStackTrace();
			if(conn!=null){
				try {
					conn.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				} 
			}
		} finally {
			this.closeAll();
		}
		return list;*/
		
		StringBuffer sqlS = new StringBuffer("select ng.groupruleid,ng.grouprulename,ng.mlocale,ng.marea from news_group_catlog ngc join news_group ng on ngc.groupruleid=ng.groupruleid join news_catlog nc on ngc.catlogid=nc.catlogid ");
		if(catlogid!=null){
			sqlS.append(" and nc.catlogid="
					+ catlogid + "");
		}
		List<NewsGroup> list=new ArrayList<NewsGroup>();
		try {
			List<Object[]> temp=this.em.createNativeQuery(sqlS.toString()).getResultList();
			NewsGroup group_temp = null;
			sqlS=new StringBuffer("select areaid,areaname from t_areainfo where areaid=?");
			Query query=em.createNativeQuery(sqlS.toString());
			for(Object[]o:temp)
			{
				group_temp=new NewsGroup();
				group_temp.setGroupruleid(this.getInteger(o[0]));
				group_temp.setGrouprulename(this.getString(o[1]));
				group_temp.setMlocale(this.getString(o[2]));
				if(o[3]!=null&&!"".equals(o[3])){
					query.setParameter(1, this.getString(o[3]));
					List<Object[]> temp2=query.getResultList();
					TAreainfo areainfo = null;
					if(temp2.size()>0){
						areainfo=new TAreainfo();
						areainfo.setAreaid(this.getInteger(temp2.get(0)[0]));
						areainfo.setAreaname(this.getString(temp2.get(0)[1]));
					}
					if(areainfo!=null){
						group_temp.setMarea(areainfo);
					}
				}
				list.add(group_temp);
			}
		
		} catch (Exception e) {
			logger.error("getSelectedgroupListbyid fail !!!" + e.getMessage());
			e.printStackTrace();
		} 
		return list;
	}
	
	public GroupCatalogVo findGroupCatalogById(Integer catlogid) {
		GroupCatalogVo groupCatalog = null;
		String hql ="SELECT n FROM NewsCatlog n where n.catlogid="+ catlogid;
		NewsCatlog catalog= (NewsCatlog) this.getSingleResult(hql);
		groupCatalog=new GroupCatalogVo();
		groupCatalog.setCatlogid(catalog.getCatlogid());
		groupCatalog.setCatlogname(catalog.getCatlogname());
		groupCatalog.setCatlogversion(catalog.getCatlogversion());
		return groupCatalog;
	}

	
	public List getAllVersion(){
		String hql = "SELECT n FROM NewsCatlog n order by n.isnowpublish, n.catlogversion desc, n.createtime desc ";
		List buttonlist = getQueryList(hql);
		return buttonlist;
	}
	/*public Object getSingleResult(Query query) {
		Object o =null;
		try {
			o = query.getSingleResult();
		} catch (NoResultException ex) {
			return null;
		} catch (NonUniqueResultException ex) {
			o = query.getResultList();
			return ((List) o).get(0);
		}
		return o;
	}*/
	
	/*public static boolean StringIsNotNull(Object str) {
        boolean b = false;
        try {
            if (!str.equals("") && !str.equals("null")) {
                b = true;
            }
        } catch (Exception e) {
        }
        return b;
    }*/
	
}
